package com.chuanghai.light.tcp;

import com.chuanghai.light.entity.TimeStruct;
import com.chuanghai.light.entity.TimeStructList;

import java.sql.*;
import java.util.HashMap;
import java.util.List;


public class MysqlTool {

    // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
    //static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    //static final String DB_URL = "jdbc:mysql://localhost:3306/new_school";

    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/new_school?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false";
    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "123456";
    static Connection conn = null;
    static Statement stmt = null;

    MysqlTool(){
        init();
    }
    public void init() {
        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            // 打开链接
            System.out.println("Connecting to a Database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            // 执行查询
            System.out.println("Instantiate the Statement object...");
            stmt = conn.createStatement();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
    }

    /**
     * 查询所有数据
     */
    public void queryAll(){
        try{
            String sql;
            sql = "SELECT id, point_code, point_name FROM light_point;";
            ResultSet rs = stmt.executeQuery(sql);

            // 展开结果集数据库
            while(rs.next()){
                // 通过字段检索
                int id  = rs.getInt("id");
                String code = rs.getString("point_code");
                String name = rs.getString("point_name");

                // 输出数据
                System.out.print("ID: " + id);
                System.out.print(",名称: " + name);
                System.out.print(",编号: " + code);
                System.out.print("\n");
            }
            // 完成后关闭
            rs.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
    }


    /**
     * 根据ip查找点位编号
     * @param ip
     * @return
     */
    public String getPointCodeByIp(String ip){
        String pointCode = null;
        try{
            String sql;
            sql = "SELECT point_code FROM light_point where ip=\""+ip+"\";";
            ResultSet rs = stmt.executeQuery(sql);
            if(rs.next())
            {
                pointCode=rs.getString("point_code");
            }
            // 完成后关闭
            rs.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
        return pointCode;
    }



    /**
     * 根据IP修改在线状态
     * @param ip
     * @return
     */
    public void setOnlineByIp(String ip){
        try{
            String sql;
            sql = "UPDATE light_point SET online = true WHERE ip=\""+ip+"\";";
            stmt.execute(sql);
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
    }

    /**
     * 设置指定点位网络状态
     * @param pointCode
     * @param online
     */
    public void updateOnlineByPointCode(String pointCode, boolean online){
        try{
            String sql;
            sql = "UPDATE light_point SET online = "+online+" WHERE point_code=\""+pointCode+"\";";
            stmt.execute(sql);
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
    }


    /**
     * 设置指定点位网络状态
     * @param pointCode
     * @param state
     */
    public void updateLightStateByPointCode(String pointCode, boolean state){
        try{
            String sql;
            sql = "UPDATE light_point SET state = "+state+" WHERE point_code=\""+pointCode+"\";";
            stmt.execute(sql);
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
    }

    /**
     * 根据code查找名字
     * @param code
     * @return
     */
    public String queryNameByCade(String code){
        String name = null;
        try{
            String sql;
            sql = "SELECT point_name FROM light_point where point_code=\""+code+"\";";
            ResultSet rs = stmt.executeQuery(sql);
//            ResultSetMetaData data = rs.getMetaData();  // 获取字段名
            if(rs.next())
            {
                name=rs.getString("point_name");
            }
            // 完成后关闭
            rs.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
        return name;
    }



    /**
     * 获取所有点位时间信息
     * @return
     */
    public HashMap<String,TimeStructList> getAllTime(){
        HashMap<String,TimeStructList> devTimeList = new HashMap<>();
        try{
            String sql;
            sql = "SELECT point_code, on_time, off_time FROM light_time_list;";
            ResultSet rs = stmt.executeQuery(sql);
            // 展开结果集数据库
            while(rs.next()){
                String code = rs.getString("point_code");
                String onTime = rs.getString("on_time");
                String offTime = rs.getString("off_time");
                // 输出数据
//                System.out.print("code: " + code);
//                System.out.print(",onTime: " + onTime);
//                System.out.print(",offTime: " + offTime);
//                System.out.print("\n");

                TimeStructList timeStructList = devTimeList.get(code);
                if(timeStructList==null)
                {
                    timeStructList = new TimeStructList();
                }
                TimeStruct onTimeStruct = new TimeStruct();
                TimeStruct offTimeStruct = new TimeStruct();
                String onTimeList[] = onTime.split(":");
                String offTimeList[] = offTime.split(":");
                onTimeStruct.hour = Integer.valueOf(onTimeList[0]);
                onTimeStruct.min = Integer.valueOf(onTimeList[1]);
                offTimeStruct.hour = Integer.valueOf(offTimeList[0]);
                offTimeStruct.min = Integer.valueOf(offTimeList[1]);
                //System.out.println( "onTime:"+onTimeStruct.hour + ":" + onTimeStruct.min);
                //System.out.println( "offTime:"+offTimeStruct.hour + ":" + offTimeStruct.min);
                timeStructList.offTime.add(offTimeStruct);
                timeStructList.onTime.add(onTimeStruct);
                devTimeList.put(code, timeStructList);
            }
            // 完成后关闭
            rs.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
        return devTimeList;
    }
}
